CREATE TABLE [dbo].[Patient]
(
[PatientCode] [nchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MiddleName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateOfBirth] [datetime] NOT NULL,
[Sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MemberType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Patient_MemberType] DEFAULT (N'Visitor'),
[MembershipSOSNumber] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MembershipSOSExpDate] [datetime] NULL,
[Nationality] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HomeStreet] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HomeWard] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HomeDistrict] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HomeCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HomeCountry] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CompanyCode] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillingAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HomePhone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MobilePhone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CompanyPhone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fax] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmailAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreateUser] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Patient_CreateDate] DEFAULT (getdate()),
[ValidCorporate] [bit] NOT NULL CONSTRAINT [DF_Patient_ValidCorporate] DEFAULT ((0)),
[DefaultPaymentMode] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Patient_DefaultPaymentMode] DEFAULT (N'CASH'),
[InsuranceCardNumber] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InsuranceCardExpDate] [datetime] NULL,
[IsDisabled] [bit] NOT NULL CONSTRAINT [DF_Patient_IsActive] DEFAULT ((0)),
[UpdateUser] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Patient_UpdateDate] DEFAULT (getdate()),
[ApptRemark] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Remark] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Patient] ADD
CONSTRAINT [FK_Patient_Country] FOREIGN KEY ([Nationality]) REFERENCES [dbo].[Country] ([CitizenName])
ALTER TABLE [dbo].[Patient] ADD
CONSTRAINT [FK_Patient_Country1] FOREIGN KEY ([HomeCountry]) REFERENCES [dbo].[Country] ([CountryName])

GO

ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED  ([PatientCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_FirstNameLastNameIsActive] ON [dbo].[Patient] ([FirstName], [LastName], [IsDisabled]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_IsActive] ON [dbo].[Patient] ([IsDisabled]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [FK_Patient_Company] FOREIGN KEY ([CompanyCode]) REFERENCES [dbo].[Company] ([CompanyCode])
GO
ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [FK_Patient_MemberType] FOREIGN KEY ([MemberType]) REFERENCES [dbo].[MemberType] ([MemberType])
GO
